/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataTier;

import EntitiesTier.ClIBGE;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Douglas
 */
public class IBGEDAO {
   private Connection conn;
    
    public IBGEDAO() throws Exception{
        
        try{
            this.conn = ConnectionFactory.getConnection();
        }
        catch(Exception e){
            throw new Exception (e.getMessage());
        }
    }
    
    public List todos() throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("SELECT * FROM tb_ibge");
            rs = ps.executeQuery();
            List<ClIBGE> lista = new ArrayList<ClIBGE>();
            while(rs.next())
            {
                String ibge_cidade = rs.getString("NM_LOCALIDADE");
                String ibge_cod = rs.getString("CD_IBGE");
                String ibge_uf = rs.getString("CD_UF");

                ClIBGE clIBGE = new ClIBGE(ibge_cidade, ibge_cod, ibge_uf);

                lista.add(clIBGE);
            }
            return lista;
        }catch(SQLException e)
        {
            throw e;
        }finally{
            ConnectionFactory.closeConnection(conn, ps, rs);
        }
    }
    
    public ClIBGE procurar_cod(String ibge_cod) throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("SELECT * FROM tb_ibge WHERE CD_IBGE = ?");
            ps.setString(1, ibge_cod);
            rs = ps.executeQuery();
            if(!rs.next())
            {
                throw new Exception("Não foi encontrado nenhum "
                        + "registro com o ID: " + ibge_cod);
            }

                String ibge_cidade = rs.getString("NM_LOCALIDADE");
                String ibge_uf = rs.getString("CD_UF");

            ClIBGE clIBGE = new ClIBGE(ibge_cidade, ibge_cod, ibge_uf);

            return clIBGE;
        }catch(SQLException e)
        {
            throw e;
        }finally{
            ConnectionFactory.closeConnection(conn, ps, rs);
        }
    }

    public List<ClIBGE> procurar(String nm_loc) throws Exception
    {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try
        {
            conn = this.conn;
            ps = conn.prepareStatement("SELECT * FROM tb_ibge Like NM_LOCALIDADE = ?");
            ps.setString(1, nm_loc);
            rs = ps.executeQuery();
            List<ClIBGE> lista = new ArrayList<ClIBGE>();
            while(rs.next())
            {
                String ibge_cidade = rs.getString("NM_LOCALIDADE");
                String ibge_cod = rs.getString("CD_IBGE");
                String ibge_uf = rs.getString("CD_UF");

                ClIBGE clIBGE = new ClIBGE(ibge_cidade, ibge_cod, ibge_uf);

                lista.add(clIBGE);
            }
            return lista;
        }catch(SQLException e)
        {
            throw e;
        }finally{
            ConnectionFactory.closeConnection(conn, ps, rs);
        }
    } 
    
}
